

'''
-- 创建设备表
CREATE TABLE devices (
    id SERIAL PRIMARY KEY,
    device_type_id INT NOT NULL,
    device_code VARCHAR(50) NOT NULL UNIQUE, -- 设备编码，唯一标识
    status VARCHAR(20) NOT NULL DEFAULT 'enable', -- 设备状态：启用 enable，故障 fault，禁用 disable
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 添加外键约束，关联 device_types 表
    FOREIGN KEY (device_type_id) REFERENCES device_types(id)
);
SELECT SETVAL('devices_id_seq',max(id),TRUE) FROM devices;
'''

from . import logger
import psycopg2

class DevicesTable:

    def __init__(self, connection, cursor):

        self.connection = connection
        self.cursor = cursor

    def create_device(self, device_type_id, device_code, status):
        try:
            self.cursor.execute("INSERT INTO devices (device_type_id, device_code, status) VALUES (%s, %s, %s)", (device_type_id, device_code, status))
            logger.info(f"Device {device_code} created successfully.")
            return True
        
        except psycopg2.errors.UniqueViolation as e:
            logger.warning(f"Device {device_code} already exists.")
            return True

        except Exception as e:
            logger.error(f"Error creating device {device_code}: {e}")
            return False
        
    def get_device_by_id(self, device_id):
        try:
            self.cursor.execute("SELECT * FROM devices WHERE id = %s", (device_id,))
            device = self.cursor.fetchone()
            logger.info(f"Got device {device_id} successfully.")
            return device
        except Exception as e:
            logger.error(f"Error getting device {device_id}: {e}")
            return None

    def get_device_by_code(self, device_code):
        try:
            self.cursor.execute("SELECT * FROM devices WHERE device_code = %s", (device_code,))
            device = self.cursor.fetchone()
            logger.info(f"Got device {device_code} successfully.")
            return device
        except Exception as e:
            logger.error(f"Error getting device {device_code}: {e}")
            return None
        

    def get_devices_by_type(self, device_type_id):
        try:
            self.cursor.execute("SELECT * FROM devices WHERE device_type_id = %s", (device_type_id,))
            devices = self.cursor.fetchall()
            logger.info(f"Got devices of type {device_type_id} successfully.")
            return devices
        except Exception as e:
            logger.error(f"Error getting devices of type {device_type_id}: {e}")
            return None
        

    def get_devices_by_status(self, status):
        try:
            self.cursor.execute("SELECT * FROM devices WHERE status = %s", (status,))
            devices = self.cursor.fetchall()
            logger.info(f"Got devices with status {status} successfully.")
            return devices
        except Exception as e:
            logger.error(f"Error getting devices with status {status}: {e}")
            return None
        

    def update_device(self, device_id, device_type_id, device_code, status):
        try:
            self.cursor.execute("UPDATE devices SET device_type_id = %s, device_code = %s, status = %s WHERE id = %s", (device_type_id, device_code, status, device_id))
            logger.info(f"Device {device_id} updated successfully.")
            return True
        except Exception as e:
            logger.error(f"Error updating device {device_id}: {e}")
            return False

    def delete_device(self, device_id):
        try:
            self.cursor.execute("DELETE FROM devices WHERE id = %s", (device_id,))
            logger.info(f"Device {device_id} deleted successfully.")
            return True
        except Exception as e:
            logger.error(f"Error deleting device {device_id}: {e}")
            return False
        

    def get_all_devices(self, device_type_id=None, device_code=None, status=None, limit=10, offset=0): # 最高权限调整

        sql = "SELECT * FROM devices ORDER BY id DESC"
        params = []
        if device_type_id:
            sql += " WHERE device_type_id = %s"
            params.append(device_type_id)
        if device_code:
            if device_type_id:

                sql += " AND device_code = %s"
            else:
                sql += " WHERE device_code = %s"
            params.append(device_code)
        
        if status:

            if device_type_id or device_code:
                sql += " AND status = %s"
            else:
                sql += " WHERE status = %s"

            params.append(status)
        sql += " LIMIT %s OFFSET %s"
        params.append(limit)
        params.append(offset)

        logger.info(f"sql: {sql}")
        logger.info(f"Getting all devices with params: {params}")
        try:
            self.cursor.execute(sql, tuple(params))
            devices = self.cursor.fetchall()
            logger.info(f"Got all devices successfully.")
            return devices
        except Exception as e:
            logger.error(f"Error getting all devices: {e}")
            return None

    def count_all_devices(self, device_type_id=None, device_code=None, status=None): # 最高权限调整

        sql = "SELECT COUNT(*) FROM devices"
        params = []
        if device_type_id:
            sql += " WHERE device_type_id = %s"
            params.append(device_type_id)
        if device_code:
            if device_type_id:

                sql += " AND device_code = %s"
            else:
                sql += " WHERE device_code = %s"
            params.append(device_code)
        
        if status:
    
            if device_type_id or device_code:
                sql += " AND status = %s"
            else:
                sql += " WHERE status = %s"

            params.append(status)

        logger.info(f"sql: {sql}")
        logger.info(f"Counting all devices with params: {params}")
        try:
            self.cursor.execute(sql, tuple(params))
            count = self.cursor.fetchone()['count']
            logger.info(f"Counted all devices successfully.")
            return count
        except Exception as e:
            logger.error(f"Error counting all devices: {e}")
            return None
